


/*
 * ALTER  the procedure for cancel the lineItem in the salesOrder 
 * and update the relative tables
 */
ALTER           proc change_order_proc
/*
 * input parameters
 */
@lineItem_id int, @salesOrder_id int, @qty int, @requested_date datetime, @unit_price float,
@amount float, @user_id varchar(10) , @user_name varchar(50), @reason varchar (50), @is_taxable int
AS
/*
 * Check if there is any change in the lineItem like price,quanity,requeste date,unitPrice
 */
declare @is_line_item_change int
SELECT @is_line_item_change = (SELECT count(*) FROM Customer_Open_Order_LineItem WHERE 
COOL_Qty = @qty and COOL_Unit_Cost = @unit_price)				
/*
 * Insert values into Customer_Change_Order_LineItem from Customer_Open_Order_LineItem
 */
if(@is_line_item_change = 0) 
begin
INSERT INTO Customer_Change_Order_LineItem (LineItem_ID, SalesOrder_ID, CCO_Line_Item_No,
	Product_ID, CCO_Qty, CCO_Unit_Cost, CCO_Amount, CCO_Requested_Date, 
	CCO_Booking_Date,CCO_Sugg_Schedule_Date_With_Substitutables,
	CCO_Sugg_Schedule_Date_Without_Substitutables,CCO_Schedule_Date, CCO_Revised_Date,CCO_Taxable)
	 (SELECT LineItem_ID, SalesOrder_ID, COOL_Line_Item_No,
		Product_ID, COOL_Qty, COOL_Unit_Cost, COOL_Amount, COOL_Requested_Date, 
		COOL_Booking_Date,COOL_Sugg_Schedule_Date_With_Substitutables,
		COOL_Sugg_Schedule_Date_Without_Substitutables,COOL_Schedule_Date,
		COOL_Revised_Date,COOL_Taxable FROM Customer_Open_Order_LineItem 
		WHERE LineItem_ID = @lineItem_id)
/*
 * Select the idenetity(previous id of row)
 */
declare @changeItem_id int
SELECT @changeItem_Id = (SELECT @@IDENTITY)
/*
 * Select the max changenumber
 */
declare @change_number int
SELECT @change_number = (SELECT MAX(CCO_Change_Number) FROM Customer_Change_Order_LineItem
WHERE LineItem_Id = @lineItem_id)
if (@change_number is null)
	select @change_number = 1
else
	select @change_number = @change_number + 1
/*
 * Update the Customer_Cancel_Order_LineItem 
 * for CancelById , CancelByName, Change_Number
 */
UPDATE Customer_Change_Order_LineItem 
	SET CCO_Change_Number = @change_number, CCO_Change_By_ID = @user_id,
	CCO_Change_By_Name = @user_name, CCO_Reason = @reason,TimeStamp = CURRENT_TIMESTAMP
	WHERE ChangeItem_ID = @changeItem_id
/*
 * Update the LineItem from the Customer_Open_Order_LineItem with quantity,price,amount,date
 */
UPDATE Customer_Open_Order_LineItem 
	SET COOL_Qty = @qty, COOL_Unit_Cost = @unit_price, COOL_amount = @amount,
	COOL_Requested_Date = @requested_date, COOL_Last_Updated_By_ID = @user_id,
	COOL_Last_Updated_By_Name = @user_name,TimeStamp = CURRENT_TIMESTAMP,COOL_Taxable = @is_taxable
	WHERE LineItem_Id = @lineItem_id
select @is_line_item_change
end
else
begin
	UPDATE Customer_Open_Order_LineItem SET COOL_Requested_Date = @requested_date
	WHERE LineItem_ID = @lineItem_id
end



